*********************************************************************************
** Replication code
** Authors: Meilin Ma, Fei Qin, Jayson Lusk
** Title: Retailer assortment under Demand Shock
** Date: 05/26/2024
** Table 3: Store product offerings in response to demand shocks
*********************************************************************************

******************
** Monthly
******************

clear all


** compute dependent variables in 2006 at the store level
use "c_3603_mnnm.dta", clear

rename type* typ*

keep if year==2006
collapse (mean)pkstr_mnnm flvstr_mnnm stlstr_mnnm typstr_mnnm prdstr_mnnm strvat_ui  upstr_mn strvat_mnnm avgupcsz_mn, by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_mnnm *_2006 
rename upstr_mn upstr_mn_2006
rename strvat_ui strvat_ui_2006
rename avgupcsz_mn avgupcsz_mn_2006

save mn2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_mnnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using mn2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
label variable flvstr_2006 "2006 avg #flv"
label variable stlstr_2006 "2006 avg #sty"
label variable typstr_2006 "2006 avg #typ"
label variable prdstr_2006 "2006 avg #prd"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_mnnm "#packages"
label variable flvstr_mnnm "#flavors"
label variable stlstr_mnnm "#styles"
label variable typestr_mnnm "#types"
label variable prdstr_mnnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_mnnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_mn)
gen lgp_2006 = log(upstr_mn_2006)

** rescale uniqueness index
qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge with SNAP data
gen mn_snap = 1 if mn < 7
replace mn_snap = 7 if mn >= 7

**
merge m:1 state county mn_snap year using "snap_yogurt.dta"

drop if _merge == 2
drop _merge

**
gen lg_snap_ppl = log(snap_ppl)
gen lg_snap_hh = log(snap_hh)
gen lg_snap_dl = log(snap_dl)





*****************************************************************
** baseline reg
** income/wealth variables at county-month level
*****************************************************************
** consider groceries and mass mechandisers, resp.
** 2006 store upc proxy shelf space and path dependence
** current market upc proxy entire product pool 
** Four dep vars

qui local x "emp lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio trend"
regress upcstr_mnnm `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m1

reg upcstr_mnnm `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m2

**
qui local x "emp lnhv_ad_r lg_snap_dl strvat_2006 upc_all cmp_cntnm pop_ad sex_ratio trend"
reg strvat_mnnm `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m3

reg strvat_mnnm `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m4

**
qui local x "emp lnhv_ad_r lg_snap_dl strvat_ui_2006 upc_all cmp_cntnm pop_ad sex_ratio trend"
reg strvat_ui `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m5

reg strvat_ui `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m6

** 
qui local x "emp lnhv_ad_r lg_snap_dl avgupcsz_mn_2006 upc_all cmp_cntnm pop_ad sex_ratio trend"
reg avgupcsz_mn `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m7

reg avgupcsz_mn `x' i.qt i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m8

**
esttab m1 m2 m3 m4 m5 m6 m7 m8, keep(emp lnhv_ad_r _cons) ///
order (emp lnhv_ad_r) ///
star(# 0.10 * 0.05 ** 0.01 *** 0.001) ///
cells(b(star fmt(3)) se(par fmt(3))) ///
legend label varlabels(_cons Constant) ///
stats(N r2 df_r)

/*
----------------------------------------------------------------------------------------------------------------------------------------------------
                              (1)             (2)             (3)             (4)             (5)             (6)             (7)             (8)   
                            #UPCs           #UPCs      #varieties      #varieties       unq index       unq index    avg amount~C    avg amount~C   
                             b/se            b/se            b/se            b/se            b/se            b/se            b/se            b/se   
----------------------------------------------------------------------------------------------------------------------------------------------------
employment rate             0.109           0.098           0.349***        0.135***        0.087           0.369**         0.015          -0.025*  
                          (0.235)         (0.208)         (0.056)         (0.027)         (0.069)         (0.108)         (0.009)         (0.012)   
log house value             4.049***        3.448**         0.431#          0.720***       -0.364          -0.287          -0.050           0.308***
                          (1.091)         (1.012)         (0.249)         (0.138)         (0.308)         (0.305)         (0.073)         (0.072)   
Constant                 -124.826***      -89.195**       -29.659***      -30.513***      101.407***      153.012***        0.488          -3.953** 
                         (27.160)        (28.280)         (5.786)         (4.079)        (10.182)        (15.121)         (1.032)         (1.313)   
----------------------------------------------------------------------------------------------------------------------------------------------------
N                      331939.000       61691.000      331919.000       61501.000      331919.000       61501.000      331939.000       61691.000   
r2                          0.889           0.902           0.737           0.816           0.362           0.727           0.752           0.657   
df_r                       34.000          33.000          34.000          33.000          34.000          33.000          34.000          33.000   
----------------------------------------------------------------------------------------------------------------------------------------------------
# p<0.10, * p<0.05, ** p<0.01, *** p<0.001

*/

** complete regression table
esttab m1 m2 m3 m4 m5 m6 m7 m8, keep(emp lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio trend _cons) ///
order (emp lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio trend) ///
star(# 0.10 * 0.05 ** 0.01 *** 0.001) ///
cells(b(star fmt(3)) se(par fmt(3))) ///
legend label varlabels(_cons Constant) ///
stats(N r2 df_r)



******************
** Quarterly
******************

clear all

** compute dependent variables in 2006 at the store level
use "c_3603_qtnm.dta", clear

keep if year==2006
collapse (mean)pkstr_qtnm strvat_qtnm strvat_ui upstr_qt avgupcsz_qt , by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_qtnm *_2006 
rename avgupcsz_qt avgupcsz_2006
rename upstr_qt upstr_qt_2006
rename strvat_ui strvat_ui_2006

save qt2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_qtnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using qt2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
*label variable flvstr_2006 "2006 avg #flv"
*label variable stlstr_2006 "2006 avg #sty"
*label variable typstr_2006 "2006 avg #typ"
*label variable prdstr_2006 "2006 avg #prd"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_qtnm "#packages"
label variable flvstr_qtnm "#flavors"
label variable stlstr_qtnm "#styles"
label variable typestr_qtnm "#types"
label variable prdstr_qtnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_qtnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_qt)
gen lgp_2006 = log(upstr_qt_2006)

qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge SNAP data
gen qt_snap = 1 if qt == 1 | qt == 2
replace qt_snap = 2 if qt == 3 | qt == 4


merge m:1 state county qt_snap year using "snap_yogurt.dta"
drop if _merge == 2
drop _merge

gen lg_snap_ppl = log(snap_ppl)
gen lg_snap_hh = log(snap_hh)
gen lg_snap_dl = log(snap_dl)



*****************************************************************
** baseline reg
** income/wealth variables at county-month level 
*****************************************************************
** consider groceries and mass mechandisers, resp.
** 2006 store upc proxy shelf space and path dependence
** current market upc proxy entire product pool 
** Four dep vars

est clear
qui local x "lnavg_wage_r lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio"
reg upcstr_qtnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m1


reg upcstr_qtnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m2

**
qui local x "lnavg_wage_r lnhv_ad_r lg_snap_dl strvat_2006 upc_all cmp_cntnm pop_ad sex_ratio"
reg strvat_qtnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m3

reg strvat_qtnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m4

**
qui local x "lnavg_wage_r lnhv_ad_r lg_snap_dl strvat_ui_2006 upc_all cmp_cntnm pop_ad sex_ratio"
reg strvat_ui `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m5

reg strvat_ui `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m6

** 
qui local x "lnavg_wage_r lnhv_ad_r lg_snap_dl avgupcsz_2006 upc_all cmp_cntnm pop_ad sex_ratio"
reg avgupcsz_qt `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m7

reg avgupcsz_qt `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m8

**
esttab m1 m2 m3 m4 m5 m6 m7 m8, keep(lnavg_wage_r lnhv_ad_r _cons) ///
order (lnavg_wage_r lnhv_ad_r) ///
star(# 0.10 * 0.05 ** 0.01 *** 0.001) ///
cells(b(star fmt(3)) se(par fmt(3))) ///
legend label varlabels(_cons Constant) ///
stats(N r2 df_r)

/*
----------------------------------------------------------------------------------------------------------------------------------------------------
                              (1)             (2)             (3)             (4)             (5)             (6)             (7)             (8)   
                            #UPCs           #UPCs      #varieties      #varieties       unq index       unq index    avg amount~C    avg amount~C   
                             b/se            b/se            b/se            b/se            b/se            b/se            b/se            b/se   
----------------------------------------------------------------------------------------------------------------------------------------------------
log wage rate               2.801          13.315***        0.747           2.870***       -1.858**         0.620          -0.260***        0.420*  
                          (2.162)         (2.643)         (0.591)         (0.382)         (0.657)         (2.039)         (0.068)         (0.162)   
log house value             4.085**         0.623           1.148**         0.215           0.325          -0.039           0.046           0.147#  
                          (1.467)         (1.249)         (0.329)         (0.190)         (0.357)         (0.464)         (0.062)         (0.079)   
Constant                 -252.025***     -149.779***       -8.003#        -19.890***       90.350***      119.012***        4.875***       -1.963   
                         (24.909)        (25.705)         (4.249)         (3.160)         (7.013)        (17.395)         (0.597)         (1.691)   
----------------------------------------------------------------------------------------------------------------------------------------------------
N                      110760.000       20899.000      110757.000       20814.000      110757.000       20814.000      110760.000       20899.000   
r2                          0.879           0.902           0.732           0.815           0.322           0.701           0.767           0.654   
df_r                       34.000          33.000          34.000          33.000          34.000          33.000          34.000          33.000   
----------------------------------------------------------------------------------------------------------------------------------------------------
# p<0.10, * p<0.05, ** p<0.01, *** p<0.001
*/


** complete table
esttab m1 m2 m3 m4 m5 m6 m7 m8, keep(lnavg_wage_r lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio _cons) ///
order (lnavg_wage_r lnhv_ad_r lg_snap_dl upcstr_2006 upc_all cmp_cntnm pop_ad sex_ratio) ///
star(# 0.10 * 0.05 ** 0.01 *** 0.001) ///
cells(b(star fmt(3)) se(par fmt(3))) ///
legend label varlabels(_cons Constant) ///
stats(N r2 df_r)


******************
** Yearly
******************

clear all

** compute dependent variables in 2006 at the store level
use "c_3603_yrnm.dta", clear

rename type* typ*

keep if year==2006
collapse (mean)pkstr_yrnm strvat_yrnm strvat_ui upstr_yr avgupcsz_yr, by(store_code_uc parent_code retailer_code store_zip3 state county dma_code)
rename *_yrnm *_2006 
rename upstr_yr upstr_yr_2006
rename strvat_ui strvat_ui_2006
rename avgupcsz_yr avgupcsz_2006

save yr2006.dta, replace


clear all

** add 2006 store level size info to income/wealth info
use "c_3603_yrnm.dta", clear

merge m:1 store_code_uc parent_code retailer_code store_zip3 state county dma_code using yr2006.dta
drop _merge

********************************************************************************
** baseline
** 2007-2010
********************************************************************************

** label var
label variable unemprate "unemployment rate"
label variable lnhv_ad_r "log house value"
label variable lnavg_wage_r "log wage rate"
label variable lnmedian_inc_r "log median inc"
label variable cmp_cntnm "#competitor stores"
label variable pop_ad "county pop"
label variable upcstr_2006 "2006 avg #UPC"
label variable brdstr_2006 "2006 avg #brd"
label variable pkstr_2006 "2006 avg #pkg"
*label variable flvstr_2006 "2006 avg #flv"
*label variable stlstr_2006 "2006 avg #sty"
*label variable typstr_2006 "2006 avg #typ"
*label variable prdstr_2006 "2006 avg #prd"
label variable upc_all "mkt #UPC"
label variable brd_all "mkt #brd"
label variable pkstr_yrnm "#packages"
label variable flvstr_yrnm "#flavors"
label variable stlstr_yrnm "#styles"
label variable typestr_yrnm "#types"
label variable prdstr_yrnm "#prod types"
label variable strvat_ui "unq index"
label variable strvat_yrnm "#varieties"

** scale var
qui replace pop_ad=pop_ad/100
qui replace cmp_cntnm=cmp_cntnm/100
gen emp = 100 - unemprate
label var emp "employment rate"
gen lgp = log(upstr_yr)
gen lgp_2006 = log(upstr_yr_2006)

qui replace strvat_ui=strvat_ui*100
qui replace strvat_ui_2006 = strvat_ui_2006*100


** merge SNAP data
merge m:1 state county year using "snap_yogurt_yr.dta"
drop if _merge == 2
drop _merge

gen lg_snap_ppl = log(snap_ppl_yr)
gen lg_snap_hh = log(snap_hh_yr)
gen lg_snap_dl = log(snap_dl_yr)

*****************************************************************
** baseline reg
** income/wealth variables at county-month level
*****************************************************************
** consider groceries and mass mechandisers, resp.
** 2006 store upc proxy shelf space and path dependence
** current market upc proxy entire product pool 
** Four dep vars

est clear
qui local x "lnmedian_inc_r lnhv_ad_r lg_snap_dl upcstr_2006 cmp_cntnm pop_ad sex_ratio"
reg upcstr_yrnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m1


reg upcstr_yrnm `x' i. year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m2

**
qui local x "lnmedian_inc_r lnhv_ad_r lg_snap_dl strvat_2006 cmp_cntnm pop_ad sex_ratio"
reg strvat_yrnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m3

reg strvat_yrnm `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m4

**
qui local x "lnmedian_inc_r lnhv_ad_r lg_snap_dl strvat_ui_2006  cmp_cntnm pop_ad sex_ratio"
reg strvat_ui `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m5

reg strvat_ui `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m6

** 
qui local x "lnmedian_inc_r lnhv_ad_r lg_snap_dl avgupcsz_2006  cmp_cntnm pop_ad sex_ratio"
reg avgupcsz_yr `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==3, cluster(state)
est store m7

reg avgupcsz_yr `x' i.year i.retailer_code i.countyfipscode if year < 2011 & year> 2006 & channel==5, cluster(state)
est store m8

**
esttab m1 m2 m3 m4 m5 m6 m7 m8, keep(lnmedian_inc_r lnhv_ad_r _cons) ///
order (lnmedian_inc_r lnhv_ad_r) ///
star(# 0.10 * 0.05 ** 0.01 *** 0.001) ///
cells(b(star fmt(3)) se(par fmt(3))) ///
legend label varlabels(_cons Constant) ///
stats(N r2 df_r)


/*
----------------------------------------------------------------------------------------------------------------------------------------------------
                              (1)             (2)             (3)             (4)             (5)             (6)             (7)             (8)   
                            #UPCs           #UPCs      #varieties      #varieties       unq index       unq index    avg amount~C    avg amount~C   
                             b/se            b/se            b/se            b/se            b/se            b/se            b/se            b/se   
----------------------------------------------------------------------------------------------------------------------------------------------------
log median inc              3.960          11.064*          1.230           2.494***        2.041*         -2.332          -0.095           0.377*  
                          (4.250)         (4.692)         (1.023)         (0.665)         (0.838)         (1.953)         (0.116)         (0.155)   
log house value             6.614*          4.001#          1.185#          0.621*         -1.134#         -0.245           0.035           0.162#  
                          (2.431)         (2.203)         (0.613)         (0.304)         (0.648)         (1.911)         (0.076)         (0.088)   
Constant                  -60.425**       -25.900          -9.688*         -4.957          20.514***       58.292**         3.198***        2.780*  
                         (16.972)        (26.867)         (4.368)         (3.715)         (3.381)        (17.890)         (0.571)         (1.068)   
----------------------------------------------------------------------------------------------------------------------------------------------------
N                       27868.000        5770.000       27868.000        5712.000       27868.000        5712.000       27868.000        5770.000   
r2                          0.874           0.900           0.748           0.836           0.276           0.638           0.797           0.727   
df_r                       34.000          33.000          34.000          33.000          34.000          33.000          34.000          33.000   
----------------------------------------------------------------------------------------------------------------------------------------------------
# p<0.10, * p<0.05, ** p<0.01, *** p<0.001


*/

